use calculator::core::schema::{Schema, FieldRole, FieldType};

#[test]
fn test_schema_from_valid_json() {
    let json = r#"{
        "name": "TestSchema",
        "comment": "Test schema",
        "tables": [
            {
                "name": "User",
                "comment": "User table",
                "fields": [
                    {
                        "name": "ID",
                        "comment": "User ID",
                        "role": "PRIMARY",
                        "type": "INT",
                        "index": true
                    },
                    {
                        "name": "Name",
                        "comment": "User name",
                        "role": "NORMAL",
                        "type": "VARCHAR"
                    }
                ]
            }
        ]
    }"#;

    let schema = Schema::from_json(json);
    assert!(schema.is_ok());
    
    let schema = schema.unwrap();
    assert_eq!(schema.name, "TestSchema");
    assert_eq!(schema.comment, "Test schema");
    assert_eq!(schema.tables.len(), 1);
    
    let table = &schema.tables[0];
    assert_eq!(table.name, "User");
    assert_eq!(table.comment, "User table");
    assert_eq!(table.fields.len(), 2);
    
    let id_field = &table.fields[0];
    assert_eq!(id_field.name, "ID");
    assert_eq!(id_field.comment, "User ID");
    assert_eq!(id_field.role, FieldRole::Primary);
    assert_eq!(id_field.field_type, FieldType::Int);
    assert!(id_field.index);
    
    let name_field = &table.fields[1];
    assert_eq!(name_field.name, "Name");
    assert_eq!(name_field.comment, "User name");
    assert_eq!(name_field.role, FieldRole::Normal);
    assert_eq!(name_field.field_type, FieldType::Varchar);
}

#[test]
fn test_schema_validation_valid_names() {
    let json = r#"{
        "name": "ValidSchema1",
        "comment": "Schema with valid names",
        "tables": [
            {
                "name": "ValidTable1",
                "comment": "Table with valid name",
                "fields": [
                    {
                        "name": "ValidField1",
                        "comment": "Field with valid name",
                        "role": "PRIMARY",
                        "type": "INT"
                    }
                ]
            }
        ]
    }"#;

    let schema = Schema::from_json(json);
    assert!(schema.is_ok());
}

#[test]
fn test_schema_validation_invalid_schema_name() {
    let json = r#"{
        "name": "invalidSchema",
        "comment": "Schema with invalid name (lowercase first letter)",
        "tables": [
            {
                "name": "ValidTable",
                "comment": "Table with valid name",
                "fields": [
                    {
                        "name": "ID",
                        "comment": "ID field",
                        "role": "PRIMARY",
                        "type": "INT"
                    }
                ]
            }
        ]
    }"#;

    let schema = Schema::from_json(json);
    assert!(schema.is_err());
    let err = schema.unwrap_err();
    assert!(err.to_string().contains("Invalid schema name"));
}

#[test]
fn test_schema_validation_invalid_table_name() {
    let json = r#"{
        "name": "ValidSchema",
        "comment": "Schema with invalid table name",
        "tables": [
            {
                "name": "invalidTable",
                "comment": "Table with invalid name (lowercase first letter)",
                "fields": [
                    {
                        "name": "ID",
                        "comment": "ID field",
                        "role": "PRIMARY",
                        "type": "INT"
                    }
                ]
            }
        ]
    }"#;

    let schema = Schema::from_json(json);
    assert!(schema.is_err());
    let err = schema.unwrap_err();
    assert!(err.to_string().contains("Invalid table name"));
}

#[test]
fn test_schema_validation_invalid_field_name() {
    let json = r#"{
        "name": "ValidSchema",
        "comment": "Schema with invalid field name",
        "tables": [
            {
                "name": "ValidTable",
                "comment": "Table with invalid field name",
                "fields": [
                    {
                        "name": "id",
                        "comment": "Field with invalid name (lowercase first letter)",
                        "role": "PRIMARY",
                        "type": "INT"
                    }
                ]
            }
        ]
    }"#;

    let schema = Schema::from_json(json);
    assert!(schema.is_err());
    let err = schema.unwrap_err();
    assert!(err.to_string().contains("Invalid field name"));
}

#[test]
fn test_schema_validation_duplicate_table_names() {
    let json = r#"{
        "name": "ValidSchema",
        "comment": "Schema with duplicate table names",
        "tables": [
            {
                "name": "User",
                "comment": "First user table",
                "fields": [
                    {
                        "name": "ID",
                        "comment": "ID field",
                        "role": "PRIMARY",
                        "type": "INT"
                    }
                ]
            },
            {
                "name": "User",
                "comment": "Second user table",
                "fields": [
                    {
                        "name": "ID",
                        "comment": "ID field",
                        "role": "PRIMARY",
                        "type": "INT"
                    }
                ]
            }
        ]
    }"#;

    let schema = Schema::from_json(json);
    assert!(schema.is_err());
    let err = schema.unwrap_err();
    assert!(err.to_string().contains("Duplicate table name"));
}

#[test]
fn test_schema_validation_duplicate_field_names() {
    let json = r#"{
        "name": "ValidSchema",
        "comment": "Schema with duplicate field names",
        "tables": [
            {
                "name": "User",
                "comment": "User table with duplicate field names",
                "fields": [
                    {
                        "name": "ID",
                        "comment": "ID field",
                        "role": "PRIMARY",
                        "type": "INT"
                    },
                    {
                        "name": "ID",
                        "comment": "Duplicate ID field",
                        "role": "NORMAL",
                        "type": "INT"
                    }
                ]
            }
        ]
    }"#;

    let schema = Schema::from_json(json);
    assert!(schema.is_err());
    let err = schema.unwrap_err();
    assert!(err.to_string().contains("Duplicate field name"));
}

#[test]
fn test_schema_validation_no_primary_key() {
    let json = r#"{
        "name": "ValidSchema",
        "comment": "Schema with no primary key",
        "tables": [
            {
                "name": "User",
                "comment": "User table with no primary key",
                "fields": [
                    {
                        "name": "Name",
                        "comment": "Name field",
                        "role": "NORMAL",
                        "type": "VARCHAR"
                    }
                ]
            }
        ]
    }"#;

    let schema = Schema::from_json(json);
    assert!(schema.is_err());
    let err = schema.unwrap_err();
    assert!(err.to_string().contains("must have exactly one primary key"));
}

#[test]
fn test_schema_validation_multiple_primary_keys() {
    let json = r#"{
        "name": "ValidSchema",
        "comment": "Schema with multiple primary keys",
        "tables": [
            {
                "name": "User",
                "comment": "User table with multiple primary keys",
                "fields": [
                    {
                        "name": "ID",
                        "comment": "ID field",
                        "role": "PRIMARY",
                        "type": "INT"
                    },
                    {
                        "name": "Code",
                        "comment": "Code field",
                        "role": "PRIMARY",
                        "type": "VARCHAR"
                    }
                ]
            }
        ]
    }"#;

    let schema = Schema::from_json(json);
    assert!(schema.is_err());
    let err = schema.unwrap_err();
    assert!(err.to_string().contains("must have exactly one primary key"));
}

#[test]
fn test_schema_validation_primary_key_invalid_type() {
    let json = r#"{
        "name": "ValidSchema",
        "comment": "Schema with primary key of invalid type",
        "tables": [
            {
                "name": "User",
                "comment": "User table with primary key of invalid type",
                "fields": [
                    {
                        "name": "ID",
                        "comment": "ID field",
                        "role": "PRIMARY",
                        "type": "FLOAT"
                    }
                ]
            }
        ]
    }"#;

    let schema = Schema::from_json(json);
    assert!(schema.is_err());
    let err = schema.unwrap_err();
    assert!(err.to_string().contains("Primary key field"));
    assert!(err.to_string().contains("must be INT or VARCHAR type"));
}

#[test]
fn test_schema_validation_foreign_key_without_references() {
    let json = r#"{
        "name": "ValidSchema",
        "comment": "Schema with foreign key but no references",
        "tables": [
            {
                "name": "User",
                "comment": "User table",
                "fields": [
                    {
                        "name": "ID",
                        "comment": "ID field",
                        "role": "PRIMARY",
                        "type": "INT"
                    }
                ]
            },
            {
                "name": "Order",
                "comment": "Order table",
                "fields": [
                    {
                        "name": "ID",
                        "comment": "Order ID",
                        "role": "PRIMARY",
                        "type": "INT"
                    },
                    {
                        "name": "UserID",
                        "comment": "User ID reference",
                        "role": "FOREIGN",
                        "type": "INT"
                    }
                ]
            }
        ]
    }"#;

    let schema = Schema::from_json(json);
    assert!(schema.is_err());
    let err = schema.unwrap_err();
    assert!(err.to_string().contains("Foreign key field"));
    assert!(err.to_string().contains("must have references"));
}

#[test]
fn test_schema_validation_formula_field_without_expression() {
    let json = r#"{
        "name": "ValidSchema",
        "comment": "Schema with formula field but no expression",
        "tables": [
            {
                "name": "Product",
                "comment": "Product table",
                "fields": [
                    {
                        "name": "ID",
                        "comment": "ID field",
                        "role": "PRIMARY",
                        "type": "INT"
                    },
                    {
                        "name": "Total",
                        "comment": "Total amount",
                        "role": "FORMULA",
                        "type": "FLOAT"
                    }
                ]
            }
        ]
    }"#;

    let schema = Schema::from_json(json);
    assert!(schema.is_err());
    let err = schema.unwrap_err();
    assert!(err.to_string().contains("Formula field"));
    assert!(err.to_string().contains("must have defaultExpr or exprField"));
}

#[test]
fn test_schema_validation_normal_field_with_expression() {
    let json = r#"{
        "name": "ValidSchema",
        "comment": "Schema with normal field having expression",
        "tables": [
            {
                "name": "Product",
                "comment": "Product table",
                "fields": [
                    {
                        "name": "ID",
                        "comment": "ID field",
                        "role": "PRIMARY",
                        "type": "INT"
                    },
                    {
                        "name": "Name",
                        "comment": "Name field",
                        "role": "NORMAL",
                        "type": "VARCHAR",
                        "defaultExpr": "Unknown"
                    }
                ]
            }
        ]
    }"#;

    let schema = Schema::from_json(json);
    assert!(schema.is_err());
    let err = schema.unwrap_err();
    assert!(err.to_string().contains("Normal field"));
    assert!(err.to_string().contains("can only have expression if it's ARRAY or MAP type"));
}

#[test]
fn test_schema_validation_precision_and_scale() {
    let json = r#"{
        "name": "ValidSchema",
        "comment": "Schema with invalid precision and scale",
        "tables": [
            {
                "name": "Product",
                "comment": "Product table",
                "fields": [
                    {
                        "name": "ID",
                        "comment": "ID field",
                        "role": "PRIMARY",
                        "type": "INT"
                    },
                    {
                        "name": "Price",
                        "comment": "Price field",
                        "role": "NORMAL",
                        "type": "FLOAT",
                        "scale": 2,
                        "precision": 1
                    }
                ]
            }
        ]
    }"#;

    let schema = Schema::from_json(json);
    assert!(schema.is_err());
    let err = schema.unwrap_err();
    assert!(err.to_string().contains("Scale of field Price cannot be greater than precision"));
}

#[test]
fn test_schema_validation_scale_without_precision() {
    let json = r#"{
        "name": "ValidSchema",
        "comment": "Schema with scale but no precision",
        "tables": [
            {
                "name": "Product",
                "comment": "Product table",
                "fields": [
                    {
                        "name": "ID",
                        "comment": "ID field",
                        "role": "PRIMARY",
                        "type": "INT"
                    },
                    {
                        "name": "Price",
                        "comment": "Price field",
                        "role": "NORMAL",
                        "type": "FLOAT",
                        "scale": 2
                    }
                ]
            }
        ]
    }"#;

    let schema = Schema::from_json(json);
    assert!(schema.is_err());
    let err = schema.unwrap_err();
    assert!(err.to_string().contains("Field Price has scale but no precision"));
}

#[test]
fn test_complete_schema_example() {
    let json = r#"{
        "name": "StudentScoreSystem",
        "comment": "学生成绩管理系统",
        "tables": [
            {
                "name": "Student",
                "comment": "学生信息表",
                "fields": [
                    {
                        "name": "ID",
                        "role": "PRIMARY",
                        "type": "INT",
                        "comment": "学生ID",
                        "index": true
                    },
                    {
                        "name": "Name",
                        "role": "NORMAL",
                        "type": "VARCHAR",
                        "comment": "学生姓名"
                    },
                    {
                        "name": "Age",
                        "role": "NORMAL",
                        "type": "INT",
                        "comment": "学生年龄",
                        "default": 0
                    },
                    {
                        "name": "EnrollDate",
                        "role": "NORMAL",
                        "type": "DATE",
                        "comment": "入学日期"
                    }
                ]
            },
            {
                "name": "Course",
                "comment": "课程信息表",
                "fields": [
                    {
                        "name": "CourseID",
                        "role": "PRIMARY",
                        "type": "INT",
                        "comment": "课程ID",
                        "index": true
                    },
                    {
                        "name": "CourseName",
                        "role": "NORMAL",
                        "type": "VARCHAR",
                        "comment": "课程名称"
                    },
                    {
                        "name": "Credits",
                        "role": "NORMAL",
                        "type": "FLOAT",
                        "comment": "学分",
                        "precision": 30,
                        "scale": 1
                    }
                ]
            },
            {
                "name": "Score",
                "comment": "成绩表",
                "fields": [
                    {
                        "name": "ScoreID",
                        "role": "PRIMARY",
                        "type": "INT",
                        "comment": "成绩记录ID",
                        "index": true
                    },
                    {
                        "name": "StudentID",
                        "role": "FOREIGN",
                        "type": "INT",
                        "comment": "关联学生ID",
                        "references": "Student.ID",
                        "onDelete": "CASCADE",
                        "onUpdate": "RESTRICT"
                    },
                    {
                        "name": "CourseID",
                        "role": "FOREIGN",
                        "type": "INT",
                        "comment": "关联课程ID",
                        "references": "Course.CourseID",
                        "onDelete": "CASCADE",
                        "onUpdate": "RESTRICT"
                    },
                    {
                        "name": "Scores",
                        "role": "NORMAL",
                        "type": "ARRAY",
                        "comment": "多次考试成绩"
                    },
                    {
                        "name": "ExtraInfo",
                        "role": "NORMAL",
                        "type": "MAP",
                        "comment": "额外信息"
                    },
                    {
                        "name": "TotalScore",
                        "role": "FORMULA",
                        "type": "FLOAT",
                        "comment": "总成绩",
                        "defaultExpr": "sum(Scores)",
                        "exprField": "CustomTotalExpr",
                        "precision": 30,
                        "scale": 1
                    },
                    {
                        "name": "CustomTotalExpr",
                        "role": "NORMAL",
                        "type": "VARCHAR",
                        "comment": "自定义总分计算公式"
                    },
                    {
                        "name": "LastUpdated",
                        "role": "NORMAL",
                        "type": "DATETIME",
                        "comment": "最后更新时间"
                    }
                ]
            }
        ]
    }"#;

    let schema = Schema::from_json(json);
    assert!(schema.is_ok());
    
    let schema = schema.unwrap();
    assert_eq!(schema.name, "StudentScoreSystem");
    assert_eq!(schema.comment, "学生成绩管理系统");
    assert_eq!(schema.tables.len(), 3);
    
    // Check Student table
    let student_table = &schema.tables[0];
    assert_eq!(student_table.name, "Student");
    assert_eq!(student_table.fields.len(), 4);
    
    // Check Course table
    let course_table = &schema.tables[1];
    assert_eq!(course_table.name, "Course");
    assert_eq!(course_table.fields.len(), 3);
    
    // Check Score table
    let score_table = &schema.tables[2];
    assert_eq!(score_table.name, "Score");
    assert_eq!(score_table.fields.len(), 8);
    
    // Check specific fields
    let total_score_field = &score_table.fields[5];
    assert_eq!(total_score_field.name, "TotalScore");
    assert_eq!(total_score_field.role, FieldRole::Formula);
    assert_eq!(total_score_field.field_type, FieldType::Float);
    assert_eq!(total_score_field.default_expr.as_ref().unwrap(), "sum(Scores)");
    assert_eq!(total_score_field.expr_field.as_ref().unwrap(), "CustomTotalExpr");
}

#[cfg(test)]
mod tests {
    use super::*;

    #[test]
    fn test_invalid_primary_key_type() {
        let json = r#"{
            "name": "User",
            "comment": "用户表",
            "tables": [
                {
                    "name": "UserTable",
                    "comment": "用户表",
                    "fields": [
                        {
                            "name": "Id",
                            "comment": "用户ID",
                            "role": "PRIMARY",
                            "type": "FLOAT",
                            "index": true
                        }
                    ]
                }
            ]
        }"#;
        
        let schema = Schema::from_json(json);
        assert!(schema.is_err());
        let err_msg = schema.err().unwrap().to_string();
        assert!(err_msg.contains("Primary key field"));
        assert!(err_msg.contains("must be INT or VARCHAR type"));
    }

    #[test]
    fn test_foreign_key_without_references() {
        let json = r#"{
            "name": "User",
            "comment": "用户表",
            "tables": [
                {
                    "name": "UserTable",
                    "comment": "用户表",
                    "fields": [
                        {
                            "name": "Id",
                            "comment": "用户ID",
                            "role": "PRIMARY",
                            "type": "INT",
                            "index": true
                        },
                        {
                            "name": "DepartmentId",
                            "comment": "部门ID",
                            "role": "FOREIGN",
                            "type": "INT"
                        }
                    ]
                }
            ]
        }"#;
        
        let schema = Schema::from_json(json);
        assert!(schema.is_err());
        let err_msg = schema.err().unwrap().to_string();
        assert!(err_msg.contains("Foreign key field"));
        assert!(err_msg.contains("must have references"));
    }

    #[test]
    fn test_formula_field_without_expression() {
        let json = r#"{
            "name": "User",
            "comment": "用户表",
            "tables": [
                {
                    "name": "UserTable",
                    "comment": "用户表",
                    "fields": [
                        {
                            "name": "Id",
                            "comment": "用户ID",
                            "role": "PRIMARY",
                            "type": "INT",
                            "index": true
                        },
                        {
                            "name": "FullName",
                            "comment": "完整姓名",
                            "role": "FORMULA",
                            "type": "VARCHAR"
                        }
                    ]
                }
            ]
        }"#;
        
        let schema = Schema::from_json(json);
        assert!(schema.is_err());
        let err_msg = schema.err().unwrap().to_string();
        assert!(err_msg.contains("Formula field"));
        assert!(err_msg.contains("must have defaultExpr or exprField"));
    }

    #[test]
    fn test_normal_field_with_expression() {
        let json = r#"{
            "name": "User",
            "comment": "用户表",
            "tables": [
                {
                    "name": "UserTable",
                    "comment": "用户表",
                    "fields": [
                        {
                            "name": "Id",
                            "comment": "用户ID",
                            "role": "PRIMARY",
                            "type": "INT",
                            "index": true
                        },
                        {
                            "name": "Name",
                            "comment": "姓名",
                            "role": "NORMAL",
                            "type": "VARCHAR",
                            "exprField": "Id"
                        }
                    ]
                }
            ]
        }"#;
        
        let schema = Schema::from_json(json);
        assert!(schema.is_err());
        let err_msg = schema.err().unwrap().to_string();
        assert!(err_msg.contains("Normal field"));
        assert!(err_msg.contains("can only have expression if it's ARRAY or MAP type"));
    }

}
